﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Dapper;
using System.Data;
using System.Linq.Expressions;
namespace DapperDemo
{
    public class DbHelper
    {
        public static readonly string connstr = "Server=.;DataBase=MyDb;uid=sa;pwd=123456";
        /// <summary>
        /// 获取sql执行影响行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns></returns>
        public static int Excute(string sql, object param)
        {
            int result = 0;
            using (IDbConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                result = conn.Execute(sql, param);
            }
            return result;
        }
        public static object Excutex(string sql, object param)
        {
            object result;
            using (IDbConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                result = conn.ExecuteScalar(sql,param);
            }
            return result;
        }
        /// <summary>
        /// 获取单个实体查询,可以是值类型如int
        /// </summary>
        /// <typeparam name="T">要获取的实体</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns></returns>
        public static T GetModel<T>(string sql, object param)
        {
            T t;
            using (IDbConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                t = conn.Query<T>(sql, param).FirstOrDefault();
            }
            return t;
        }
        /// <summary>
        /// 获取指定类型集合,可以是值类型
        /// </summary>
        /// <typeparam name="T">指定类型</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns></returns>
        public static IEnumerable<T> GetModelList<T>(string sql, object param)
        {
            IEnumerable<T> result=null;
            using (IDbConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                result= conn.Query<T>(sql, param);
            }
            return result;
        }
        /// <summary>
        /// 获取两表查询(一对一，一对多，多对多)
        /// </summary>
        /// <typeparam name="TModel1">表一对应实体类</typeparam>
        /// <typeparam name="TModel2">表二对应实体类</typeparam>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <param name="condition">类型关系</param>
        /// <param name="splitOn">分割字段（该字段前的列映射到模型1之后的字段映射到模型2）</param>
        /// <returns></returns>
        public static IEnumerable<TModel1> MultiQuery<TModel1,TModel2>(string sql,object param,Func<TModel1,TModel2,TModel1>condition,string splitOn="Id")
        {
            using (IDbConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                var mod = conn.Query<TModel1, TModel2, TModel1>(sql, condition, param, splitOn: splitOn);
                return mod;
            }
        }
        /// <summary>
        /// 返回动态结果，用于多个字段返回且与实体类型字段不匹配查询或三个表及其以上查询
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">sql参数</param>
        /// <returns></returns>
        public static IEnumerable<dynamic> Query(string sql, object param)
        {
            using (IDbConnection conn = new SqlConnection(connstr))
            {
                return conn.Query(sql,param);
            }
        }

    }
}
